package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Measurement;
import database.DBManager;

public class MeasurementsDao {

	private String sqlGetS   	   	= "SELECT * FROM measurement WHERE sensorID = ? ORDER BY date DESC";
	private String sqlGetD   	   	= "SELECT * FROM measurement WHERE date = ? AND sensorID = ?";
	private String sqlGet   	   	= "SELECT * FROM measurement WHERE date = ?";
	private String sqlCreate   	   	= "INSERT INTO measurement(sensorID, date, value) VALUES(?, NOW(), ?)";
	private String sqlDelete   	   	= "DELETE * FROM measurement WHERE sensorID = ? AND date = ?";
	private String sqlGetT			= "SELECT sensorID, MAX(date), value FROM measurement WHERE sensorID = ?";
	private String sqlGetDate		= "SELECT * FROM measurement WHERE sensorID = ? AND date BETWEEN ? AND ? ";
	
	private Connection        con     	 = null;
	private PreparedStatement psGetD   	 = null;
	private PreparedStatement psGetS     = null;
	private PreparedStatement psGet      = null;
	private PreparedStatement psGetT     = null;
	private PreparedStatement psCreate   = null;
	private PreparedStatement psDelete   = null;
	private PreparedStatement psGetDate  = null;

	public MeasurementsDao(DBManager myDb){
		setStatements(myDb);
	}
	
	private void setStatements(DBManager myDb){
		con = myDb.getConnection();
		try{
			this.psGetD   = con.prepareStatement(sqlGetD);
			this.psGetS   = con.prepareStatement(sqlGetS);
			this.psGet    = con.prepareStatement(sqlGet);
			this.psCreate = con.prepareStatement(sqlCreate);
			this.psDelete = con.prepareStatement(sqlDelete);
			this.psGetT   = con.prepareStatement(sqlGetT);
			this.psGetDate= con.prepareStatement(sqlGetDate);
			
		} catch(SQLException se) {
			se.printStackTrace();
		}
	}
	
	public ArrayList<Measurement> getMeasurements(int getSensorID){
		ArrayList<Measurement> measurements = new ArrayList<Measurement>();
		try{
			psGetS.clearParameters();
			psGetS.setInt(1, getSensorID);
			ResultSet rs = psGetS.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				double value = rs.getDouble(3);
				Measurement measurement = new Measurement(sensorID, date, value);
				measurements.add(measurement);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return measurements;
	}
	
	public Measurement getMeasurementByDateAndID(Date getDate, int getSensorID){
		Measurement measurement = null;
		try{
			psGetD.clearParameters() ;
			psGetD.setDate(1, getDate);
			psGetD.setInt(2, getSensorID);
			ResultSet rs = psGetD.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				double value = rs.getDouble(3);
				measurement = new Measurement(sensorID, date, value);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return measurement;
	}
	
	public Measurement getMeasurementBeforeDateWithID(Date getDate, int getSensorID){
		Measurement m = null;
		ArrayList<Measurement> measurements = new ArrayList<Measurement>();
		try{
			psGetS.clearParameters();
			psGetS.setInt(1, getSensorID);
			ResultSet rs = psGetS.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				date.setTime(rs.getTime(2).getTime() + date.getTime());		
				double value = rs.getDouble(3);
				Measurement measurement = new Measurement(sensorID, date, value);
				measurements.add(measurement);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		for(Measurement x: measurements){
			if(x.getMeasureDate().before( getDate) ){
				m = x;		
				break;
			}
		}
		return m;
	}
	
	public Measurement getLatestMeasurementByID(int getSensorID){
		Measurement measurement = null;
		try{
			psGetT.clearParameters() ;
			psGetT.setInt(1, getSensorID);
			ResultSet rs = psGetT.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				double value = rs.getDouble(3);
				measurement = new Measurement(sensorID, date, value);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return measurement;
	}
	
	public ArrayList<Measurement> getMeasurementByDate(Date getDate){
		ArrayList<Measurement> measurements = new ArrayList<Measurement>();
		try{
			psGet.clearParameters() ;
			psGet.setDate(1, getDate);
			ResultSet rs = psGet.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				double value = rs.getDouble(3);
				Measurement measurement = new Measurement(sensorID, date, value);
				measurements.add(measurement);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return measurements;
	}
	
	public ArrayList<Measurement> getMeasurementsBetweenDates(int getSensorID, Date van, Date tot){
		ArrayList<Measurement> measurements = new ArrayList<Measurement>();
		try{
			psGetDate.clearParameters() ;
			psGetDate.setInt(1, getSensorID);
			psGetDate.setDate(2, van);
			psGetDate.setDate(3, tot);
			ResultSet rs = psGetDate.executeQuery();
			while (rs.next()) {
				int sensorID = rs.getInt(1);
				Date date = rs.getDate(2);
				date.setTime(rs.getTime(2).getTime() + date.getTime());				
				double value = rs.getDouble(3);
				Measurement measurement = new Measurement(sensorID, date, value);
				measurements.add(measurement);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return measurements;
	}
	
	public boolean createMeasurement(int sensorID, double value) {
		boolean succeeded = false;
		try {
			psCreate.clearParameters();
			psCreate.setInt( 1, sensorID );
			psCreate.setDouble( 2, value );
			if( psCreate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	public boolean deleteMeasurement(int sensorID, Date date) {
		boolean succeeded = false;
		try {
			psDelete.clearParameters();
			psDelete.setInt( 1, sensorID );
			psDelete.setDate( 1, date );
			if( psDelete.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
}
